Imports System.Data.SqlClient
Public Class Families

    Public Shared Function GetFamily() As DataTable
        Str = " SELECT FAMILY_ID,Name=ENGLISH_NAME +' ( '+KHMER_NAME+' )',RELATIVE_NAME,DOB,OCCUPATION,NOTE,PHONE,ADDRESS,RELATIVE_TYPE,EMAIL,ACTIVE FROM  VIEW_FAMILY  WHERE ACTIVE=1 ORDER BY FAMILY_ID ASC"
        Try

            Dim dataAdapter As SqlDataAdapter
            dataAdapter = New SqlDataAdapter(Str, CType(Dbinterface.Connection, SqlConnection))
            Dim dataTable As DataTable = New DataTable()
            dataAdapter.Fill(dataTable)
            Return dataTable
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return Nothing
        End Try
    End Function
    Public Shared Sub AddFamily(ByVal fm As FamilyInfo)


        CmdPro.Begin()

        Try
            CmdPro.Parameters("@employeeId") = fm.EmployeeId
            CmdPro.Parameters("@relativeName") = fm.RelativeName
            CmdPro.Parameters("@dOB") = fm.dOB
            CmdPro.Parameters("@occupation") = fm.occupation
            CmdPro.Parameters("@note") = fm.note
            CmdPro.Parameters("@phone") = fm.phone
            CmdPro.Parameters("@address") = fm.address
            CmdPro.Parameters("@relativeType") = fm.relativeType
            CmdPro.Parameters("@email") = fm.email
            CmdPro.Parameters("@active") = fm.active
            fm.FamilyId = CmdPro.ExecuteNonQuery("InsertFamily", "@id")
            CmdPro.Commit()

        Catch ex As Exception

            CmdPro.Rollback()


            MsgBox(ex.Message)


        End Try


    End Sub




    Public Shared Sub UpdateFamily(ByVal fm As FamilyInfo)


        CmdPro.Begin()

        Try


            CmdPro.Parameters("@id") = fm.FamilyId
            CmdPro.Parameters("@employeeId") = fm.EmployeeId
            CmdPro.Parameters("@relativeName") = fm.RelativeName
            CmdPro.Parameters("@dOB") = fm.Dob
            CmdPro.Parameters("@occupation") = fm.Occupation
            CmdPro.Parameters("@note") = fm.Note
            CmdPro.Parameters("@phone") = fm.Phone
            CmdPro.Parameters("@address") = fm.Address
            CmdPro.Parameters("@relativeType") = fm.RelativeType
            CmdPro.Parameters("@email") = fm.Email
            CmdPro.Parameters("@active") = fm.Active
            fm.FamilyId = CmdPro.ExecuteNonQuery("UpdateFamily")

            CmdPro.Commit()
        Catch ex As Exception
            CmdPro.Rollback()
            MsgBox(ex.Message)
        End Try
    End Sub
    Public Shared Sub DeleteFamily(ByVal fm As FamilyInfo)
        CmdPro.Begin()
        Try
            CmdPro.Parameters("@id") = fm.FamilyId
            CmdPro.ExecuteNonQuery("DeleteFamily")

            CmdPro.Commit()
        Catch ex As Exception
            CmdPro.Rollback()
            MsgBox(ex.Message)
        End Try
    End Sub

End Class